package com.lolongmaps.test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import com.lolongmaps.model.Restaurant;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper{
	
	String DB_PATH = null;
	private static String DB_NAME = "NRestoList";
	private SQLiteDatabase myDataBase;
	private final Context myContext;
	
	Sorter sorter;
	
	Cursor foodCursor, foodIDCursor, menuIDCursor, restaurantIDCursor, restaurantCursor; 
	
	private String restoName, restoLocation, restoArea, restoContactDetails, restoType, restoPrice;
	private int foodNumber, menuCounter, restaurantCounter, counter, filteredRestoIdFromMenu, restoId,
		filteredRestoIdFromRestaurant, delivery, wifi, open24hrs, parking, creditCard, buffet, catering, functions, reservation, entertainment, veganFood, distance;
	
	private Restaurant[] restaurants;
	private ArrayList<Integer> applicableRestoMenuBased = new ArrayList<Integer>();
	private ArrayList<Integer> finalFilteredRestaurantIds = new ArrayList<Integer>();
	private ArrayList<Restaurant> usersRestaurantChoiceList = new ArrayList<Restaurant>();
	
	public DBHelper(Context context) {
		
		super(context, DB_NAME, null, 1);
		this.myContext = context;
		DB_PATH = "/data/data/" + context.getPackageName() + "/" + "databases/";
		
		this.menuCounter = 0;
		this.restaurantCounter = 0;
		this.counter = 0;
		
		this.foodCursor = null;
		this.foodIDCursor = null;
		this.menuIDCursor = null;
		this.restaurantIDCursor = null;
		this.restaurantCursor = null;
		
	}

	
    public void createDataBase() throws IOException{
   	 
        boolean dbExist = checkDataBase();
 
        if(dbExist){
            //do nothing - database already exist
        }else{
 
            this.getReadableDatabase();
 
            try {
 
                copyDataBase();
 
            } catch (IOException e) {
 
                throw new Error("Error copying database");
 
            }
        }
 
    }
 
 
    private boolean checkDataBase() {
 
        SQLiteDatabase checkDB = null;
 
        try {
        	
            String myPath = DB_PATH + DB_NAME;
            checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
        } catch(SQLiteException e) {
 
 
        }
 
        if(checkDB != null) {
 
            checkDB.close();
 
        }
 
        return checkDB != null ? true : false;
        
    }
	
    private void copyDataBase() throws IOException {
    	 
        InputStream myInput = myContext.getAssets().open(DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        OutputStream myOutput = new FileOutputStream(outFileName);
        
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myInput.read(buffer))>0){
            myOutput.write(buffer, 0, length);
        }
 
        myOutput.flush();
        myOutput.close();
        myInput.close();
 
    }
	
	public void openDataBase() throws SQLException {
		
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
		
	}
	
	public synchronized void close() {
		
		if(myDataBase != null) {
			
			myDataBase.close();
			
			super.close();
			
		}
		
	}

	public void onCreate(SQLiteDatabase db) { }

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }
	
	public Cursor rawQuery(String sql, String[] selectionArgs) {
		
		return myDataBase.rawQuery(sql, selectionArgs);
		
	}
	
	
	public void filterFood(String foodName) {
		
		try {
			
			createDataBase();
			
		} catch (IOException e) {
			
			e.printStackTrace();
		}
		
		openDataBase();
		
		foodIDCursor = rawQuery("SELECT _idFood FROM Food WHERE name='" + foodName + "'", null);
		foodIDCursor.moveToFirst();
		
		do{
			
			foodNumber = foodIDCursor.getInt((foodIDCursor.getColumnIndex("_idFood")));
			System.out.println(foodNumber);
			
		} while(foodIDCursor.moveToNext() != false);
		
		foodIDCursor.close();
		close();
		
	}
	
	public void filterMenu(int foodId) {
		
		openDataBase();
		menuIDCursor = rawQuery("SELECT _idMenu FROM Menu WHERE _idFood=" + foodNumber, null);
		menuIDCursor.moveToFirst();
		
		if(menuIDCursor != null && menuIDCursor.moveToFirst()) {
			
			do {
				
				filteredRestoIdFromMenu = menuIDCursor.getInt(menuIDCursor.getColumnIndex("_idMenu"));
				applicableRestoMenuBased.add(filteredRestoIdFromMenu);
				
				menuCounter++;
				
			} while(menuIDCursor.moveToNext() != false);
			
		}
		
		menuIDCursor.close();
		close();
		
	}
	
	public void filterRestaurant(int idResto, String userChosenType, String userChosenPrice, String userChosenArea){
		
		openDataBase();
		restaurantIDCursor = rawQuery("SELECT _idRestaurant FROM Restaurant WHERE _idRestaurant=" + idResto + " AND type='" + userChosenType + "' AND price<=" + userChosenPrice + " AND area='" + userChosenArea + "'", null);
		
		while(restaurantIDCursor.moveToNext()){
			
			filteredRestoIdFromRestaurant = restaurantIDCursor.getInt(restaurantIDCursor.getColumnIndex("_idRestaurant"));
			finalFilteredRestaurantIds.add(filteredRestoIdFromRestaurant);

			restaurantCounter++;
		}
		
		restaurantIDCursor.close();
		close();
		
	}
	
	public void populateChoiceList(int finalFilteredId){
		
		openDataBase();
		restaurants = new Restaurant[getFinalFilteredRestaurantIdsContainer().size()];
		restaurantCursor = rawQuery("SELECT * FROM Restaurant WHERE _idRestaurant=" + finalFilteredId, null);
		
		while(restaurantCursor.moveToNext()){
			
			restoId = restaurantCursor.getInt(restaurantCursor.getColumnIndex("_idRestaurant"));
			restoName = restaurantCursor.getString(restaurantCursor.getColumnIndex("name"));
			restoLocation = restaurantCursor.getString(restaurantCursor.getColumnIndex("location"));
			restoArea = restaurantCursor.getString(restaurantCursor.getColumnIndex("area"));
			restoContactDetails = restaurantCursor.getString(restaurantCursor.getColumnIndex("contactDetails"));
			restoPrice = restaurantCursor.getString(restaurantCursor.getColumnIndex("price"));
			restoType = restaurantCursor.getString(restaurantCursor.getColumnIndex("type"));
			this.delivery = restaurantCursor.getInt(restaurantCursor.getColumnIndex("delivery"));
			this.wifi = restaurantCursor.getInt(restaurantCursor.getColumnIndex("wifiAccess"));
			this.open24hrs = restaurantCursor.getInt(restaurantCursor.getColumnIndex("open24hrs"));
			this.creditCard = restaurantCursor.getInt(restaurantCursor.getColumnIndex("creditCard"));
			this.buffet = restaurantCursor.getInt(restaurantCursor.getColumnIndex("buffet"));
			this.catering = restaurantCursor.getInt(restaurantCursor.getColumnIndex("catering"));
			this.functions = restaurantCursor.getInt(restaurantCursor.getColumnIndex("functions"));
			this.reservation = restaurantCursor.getInt(restaurantCursor.getColumnIndex("reservation"));
			this.entertainment = restaurantCursor.getInt(restaurantCursor.getColumnIndex("entertainment"));
			this.veganFood = restaurantCursor.getInt(restaurantCursor.getColumnIndex("offersVeganFood"));
			this.distance = restaurantCursor.getInt(restaurantCursor.getColumnIndex("distance_m"));
			
			restaurants[counter] = new Restaurant(restoId, restoName, restoLocation, restoArea, restoContactDetails, restoPrice, restoType,
												this.delivery, this.wifi, this.open24hrs, this.parking, this.creditCard, this.buffet,
												this.catering, this.functions, this.reservation, this.entertainment, this.veganFood, this.distance);
			
			usersRestaurantChoiceList.add(restaurants[counter]);
			counter++;
			
		}
		
		restaurantCursor.close();
		close();
		
		
	}
	


	public int getFoodNumber(){
		
		return foodNumber;
		
	}
	
	public ArrayList<Integer> getApplicableRestoMenuBasedContainer(){
		
		return applicableRestoMenuBased;
		
	}
	
	public ArrayList<Integer> getFinalFilteredRestaurantIdsContainer(){
		
		return finalFilteredRestaurantIds;
		
	}
	
	public ArrayList<Restaurant> getUsersRestaurantChoiceListContainer(){
		
		return usersRestaurantChoiceList;
		
	}
	
	public void setUsersRestaurantChoiceList(ArrayList<Restaurant> r) {
		
		this.usersRestaurantChoiceList = r;
		
	}
	
}
